ALTER PROCEDURE [dbo].[selectSitesDisturbancesDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

	DECLARE @startDate DateTime = @EventDate
	DECLARE @endDate DateTime

	if @context = 'day'
		SET @endDate = DATEADD(DAY, 1, @startDate);
	if @context = 'hour'
		SET @endDate = DATEADD(HOUR, 1, @startDate);
	if @context = 'minute'
		SET @endDate = DATEADD(MINUTE, 1, @startDate);
	if @context = 'second'
		SET @endDate = DATEADD(SECOND, 1, @startDate);

	DECLARE @PivotColumns NVARCHAR(MAX) = N''
	DECLARE @ReturnColumns NVARCHAR(MAX) = N''
	DECLARE @SQLStatement NVARCHAR(MAX) = N''

	create table #TEMP (Name varchar(max))
	insert into #TEMP SELECT SeverityCode FROM (Select Distinct SeverityCode FROM DisturbanceSeverity) as t

	SELECT @PivotColumns = @PivotColumns + '[' + COALESCE(CAST(Name as varchar(5)), '') + '],' 
	FROM #TEMP ORDER BY Name desc

	SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + COALESCE(CAST(Name as varchar(5)), '') + '], 0) AS [' + COALESCE(CAST(Name as varchar(5)), '') + '],' 
	FROM #TEMP ORDER BY Name desc
	DECLARE @voltageEnvelope varchar(max) = (SELECT TOP 1 Value FROM Setting WHERE Name = 'DefaultVoltageEnvelope')

	SET @SQLStatement =
	'SELECT *
    INTO #authMeters
    FROM authMeters(@username)

    SELECT *
    INTO #meterSelections
    FROM String_To_Int_Table(@MeterID, '','')

    SELECT (SELECT TOP 1 ID FROM Event WHERE MeterID = pvt.MeterID AND StartTime >= @startDate AND StartTime < @endDate)
			 EventID, 
			 MeterID, 
			 Site, 
			 ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
	 FROM ( 
		SELECT 
			Event.MeterID, COUNT(*) AS EventCount, SeverityCode, Meter.Name as Site 
		FROM 
			Disturbance JOIN 
			DisturbanceSeverity ON Disturbance.ID = DisturbanceSeverity.DisturbanceID JOIN
			Event ON Disturbance.EventID = Event.ID JOIN
			EventType ON Event.EventTypeID = EventType.ID JOIN
			Meter ON Event.MeterID = Meter.ID JOIN
			Phase ON Phase.ID = Disturbance.PhaseID JOIN
			VoltageEnvelope ON VoltageEnvelope.ID = DisturbanceSeverity.VoltageEnvelopeID	
		WHERE
			Phase.Name = ''Worst'' AND 
    		MeterID in (select * from #authMeters) AND MeterID IN (SELECT * FROM #meterSelections) AND 
			VoltageEnvelope.Name = COALESCE(@voltageEnvelope, ''ITIC'') AND										 
			Event.StartTime >= @startDate AND Event.StartTime < @endDate  
	    GROUP BY Event.MeterID,Meter.Name,SeverityCode
	       ) as ed 
	 PIVOT( 
			SUM(ed.EventCount)
			FOR ed.SeverityCode IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ') 
	 ) as pvt
	 ORDER BY MeterID '

	print @startDate
	print @endDate
	print @sqlstatement
	exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME, @voltageEnvelope VARCHAR(MAX) ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate, @voltageEnvelope = @voltageEnvelope
END

GO

ALTER PROCEDURE [dbo].[selectSitesFaultsDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startDate DateTime = @EventDate
    DECLARE @endDate DateTime

    if @context = 'day'
        SET @endDate = DATEADD(DAY, 1, @startDate);
    if @context = 'hour'
        SET @endDate = DATEADD(HOUR, 1, @startDate);
    if @context = 'minute'
        SET @endDate = DATEADD(MINUTE, 1, @startDate);
    if @context = 'second'
        SET @endDate = DATEADD(SECOND, 1, @startDate);

    SELECT *
    INTO #authMeters
    FROM authMeters(@username)
    
    SELECT *
    INTO #meterSelections
    FROM String_to_int_table(@MeterID, ',')

    ; WITH FaultDetail AS
    (
        SELECT
            FaultSummary.ID AS thefaultid,
            Meter.Name AS thesite,
            Meter.ShortName AS theshortsite,
            MeterLocation.ShortName AS locationname,
            Meter.ID AS themeterid,
            Line.ID AS thelineid,
            Event.ID AS theeventid,
            MeterLine.LineName AS thelinename,
            Line.VoltageKV AS voltage,
            CAST(CAST(Event.StartTime AS TIME) AS NVARCHAR(100)) AS theinceptiontime,
            FaultSummary.FaultType AS thefaulttype,
            CASE WHEN FaultSummary.Distance = '-1E308' THEN 'NaN' ELSE CAST(CAST(FaultSummary.Distance AS DECIMAL(16,2)) AS NVARCHAR(19)) END AS thecurrentdistance,
            (SELECT COUNT(*) FROM FaultNote WHERE FaultSummary.ID = FaultNote.FaultSummaryID) as notecount,
            ROW_NUMBER() OVER(PARTITION BY Event.ID ORDER BY FaultSummary.IsSuppressed, FaultSummary.IsSelectedAlgorithm DESC, FaultSummary.Inception) AS rk
        FROM
            FaultSummary JOIN
            Event ON FaultSummary.EventID = Event.ID JOIN
            EventType ON Event.EventTypeID = EventType.ID JOIN
            Meter ON Event.MeterID = Meter.ID JOIN
            MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN
            Line ON Event.LineID = Line.ID JOIN
            MeterLine ON MeterLine.MeterID = Meter.ID AND MeterLine.LineID = Line.ID
        WHERE
            EventType.Name = 'Fault' AND
            Event.StartTime >= @startDate AND Event.StartTime < @endDate AND
            Meter.ID IN (SELECT * FROM #meterSelections) AND
            Meter.ID IN (select * from #authMeters)
    )
    SELECT *
    FROM FaultDetail
    WHERE rk = 1
END
GO

ALTER PROCEDURE [dbo].[selectSitesExtensionsDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(MAX),
    @username as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startDate DateTime = @EventDate
    DECLARE @endDate DateTime

    if @context = 'day'
        SET @endDate = DATEADD(DAY, 1, @startDate);
    if @context = 'hour'
        SET @endDate = DATEADD(HOUR, 1, @startDate);
    if @context = 'minute'
        SET @endDate = DATEADD(MINUTE, 1, @startDate);
    if @context = 'second'
        SET @endDate = DATEADD(SECOND, 1, @startDate);

    DECLARE @PivotColumns NVARCHAR(MAX) = N''
    DECLARE @ReturnColumns NVARCHAR(MAX) = N''
    DECLARE @SQLStatement NVARCHAR(MAX) = N''
    DECLARE @MiddleStatment NVARCHAR(MAX) = N''

    SELECT @PivotColumns = @PivotColumns + '[' + t.ServiceName + '],'
    FROM (Select ServiceName FROM EASExtension) AS t

    SELECT @ReturnColumns = @ReturnColumns + ' COALESCE([' + t.ServiceName + '], 0) AS [' + t.ServiceName + '],'
    FROM (Select ServiceName FROM EASExtension) AS t

    SELECT @MiddleStatment = @MiddleStatment +  '
            SELECT MeterID,
                '''+ t.ServiceName + ''' as ServiceName,
                Count(*) as EventCount
            FROM    #temp
            WHERE   dbo.' + t.HasResultFunction + '(ID) != ''''
            GROUP BY MeterID UNION'

    FROM (Select * FROM EASExtension) AS t

    SET @SQLStatement =
    'SELECT *
    INTO #authMeters
    FROM authMeters(@username)
    
    SELECT *
    INTO #meterSelections
    FROM String_To_Int_Table(@MeterID, '','')
    
    SELECT * INTO #temp
    FROM EVENT
    WHERE   StartTime Between @startDate AND @endDate AND
            MeterID in (select * from authMeters(@username)) AND
            MeterID IN (SELECT * FROM String_To_Int_Table( @MeterID,  '',''))

    SELECT (SELECT TOP 1 ID FROM Event WHERE MeterID = Meter.ID AND StartTime >= @startDate AND StartTime < @endDate) as EventID,
             Meter.ID as MeterID,
             Meter.Name as Site,
             ' + SUBSTRING(@ReturnColumns,0, LEN(@ReturnColumns)) + '
     FROM Meter Join
        (
            ' + SUBSTRING(@MiddleStatment,0, LEN(@MiddleStatment) - LEN('UNION')) + '
         ) as ed
         PIVOT(
                SUM(ed.EventCount)
                FOR ed.ServiceName IN(' + SUBSTRING(@PivotColumns,0, LEN(@PivotColumns)) + ')
         ) as pvt ON pvt.MeterID = Meter.ID
     WHERE
        Meter.ID in (select * from #authMeters) AND Meter.ID IN (SELECT * FROM #meterSelections)

     ORDER BY MeterID
     DROP Table #temp
     '

    --print @startDate
    --print @endDate
    print @sqlstatement
    exec sp_executesql @SQLStatement, N'@username nvarchar(4000), @MeterID nvarchar(MAX), @startDate DATETIME, @endDate DATETIME ', @username = @username, @MeterID = @MeterID, @startDate = @startDate, @endDate = @endDate

END
GO